package com.ym.wcp.framework.utils;

import org.apache.commons.lang.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

public class ExcelUtil {

    private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐
    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");//格式化文本，整形，常规
    private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");
    private static final DecimalFormat DECIMAL_FORMAT_NUMBER = new DecimalFormat("0.00E000"); //格式化科学计数器
    private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 读取 office excel
     *
     * @param inputStream
     * @param fileName    文件名
     * @return
     * @throws IOException
     */
    public static List<List<Object>> readExcel(InputStream inputStream, String fileName) throws Exception {
        List<List<Object>> list = new LinkedList<>();
        Workbook workbook;
        if (StringUtils.isEmpty(fileName)) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            //获取文件扩展名
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            if (XLS.equalsIgnoreCase(suffix)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (XLSX.equalsIgnoreCase(suffix)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new Exception("模板文件类型不正确");
            }
        }
        int sheetsNumber = workbook.getNumberOfSheets();
        for (int n = 0; n < sheetsNumber; n++) {
            Sheet sheet = workbook.getSheetAt(n);
            Object value;
            Row row;
            Cell cell;
            Row firstRow = sheet.getRow(sheet.getFirstRowNum());
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
                row = sheet.getRow(i);
                if (StringUtils.isEmpty(row)) {
                    continue;
                }
                List<Object> linked = new LinkedList<>();
                for (int j = row.getFirstCellNum(); j <= firstRow.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        try {
                            value = getCellValue(cell);
                        } catch (Exception e) {
                            value = "";
                        }
                    } else {
                        value = "";
                    }
                    linked.add(value);
                }
                list.add(linked);
            }
        }
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(inputStream);
        return list;
    }

    /**
     * 获取excel 单元格数据
     *
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        Object value = null;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) { //日期
                    value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
                } else if ("@".equals(cell.getCellStyle().getDataFormatString())
                        || "General".equals(cell.getCellStyle().getDataFormatString())
                        || "0_ ".equals(cell.getCellStyle().getDataFormatString())) {
                    //文本  or 常规 or 整型数值
                    value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
                } else if (POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()) { //正则匹配小数类型
                    value = cell.getNumericCellValue();  //直接显示
                } else if ("0.00E+00".equals(cell.getCellStyle().getDataFormatString())) {//科学计数
                    value = cell.getNumericCellValue();    //待完善
                    value = DECIMAL_FORMAT_NUMBER.format(value);
                } else if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) {//百分比
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_PERCENT.format(value);
                } else if ("# ?/?".equals(cell.getCellStyle().getDataFormatString())) {//分数
                    value = cell.getNumericCellValue(); ////待完善
                } else { //货币
                    value = DecimalFormat.getCurrencyInstance().format(value);
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                value = value.toString();
                break;
            default:
                value = cell.toString();
        }
        return value;
    }

    public static XSSFWorkbook createExcel(String[] headList,
                                           String[] fieldList,
                                           List<Map<String, Object>> dataList) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultColumnWidth(20);
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headList.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(headList[i]);
        }
        try {
            for (int n = 0; n < dataList.size(); n++) {
                XSSFRow row_value = sheet.createRow(n + 1);
                Map<String, Object> dataMap = dataList.get(n);
                for (int i = 0; i < fieldList.length; i++) {
                    Object o = dataMap.get(fieldList[i]);
                    XSSFCell cell = row_value.createCell(i);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(o == null ? "" : o.toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }
}
